Moving Averages

Now that you can extract data from a database, let's talk through the next step of completing the project: moving averages.

Moving averages are used to smooth out data to make it easier to observe long term trends and not get lost in daily fluctuations. For example, let's say you wanted to visualize the sales trend at a clothing retail store. You start with daily data, and your chart looks too volatile to interpret because more people shop on the weekends, so sales spike on those days.

You could sum up sales by week, but that may take out some of the detail you wanted to see.

Using a moving average, you can both smooth out the daily volatility and allow you to observe the long term trend.

Moving Averages in Spreadsheets

Let's look at how to calculate a moving average in a spreadsheet. In this exercise, we'll calculate the moving average that is shown in the image above. The data contains daily sales data from January 1, 2009 to March 31, 2009. You can find the data for this example in this Google doc , or in the CSV file linked at the bottom of this page.

To start, create a second column called 7-day MA , which is where the moving average field will be stored. Go down to the seventh day (1/7/2009) and use the AVERAGE() function to calculate the average sales for the first seven days of sales, as seen in the image below.

Now you can use Copy + Paste, Ctrl + D, or click and drag the formula down to the next cell. If you look at the new formula, you'll see that now it calculates the average sales for the second through eighth days.

You can continue this process all the way to the end of the dataset. Copy the cell and paste it all the way down to the end of the data (through row 91). Once you've done that, answer the quiz below.